package com.szholly.pro.watergas_server.ps.android.common;

import java.util.ArrayList;
import java.util.List;

import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.ps.data.OperationGroupRow;
import com.szholly.utils.util.StringUtils;
import org.springframework.util.CollectionUtils;


public class CommonSqlUtils {

	/**
	 * 获取组信息
	 * 
	 * @return null表示没有组信息
	 */
	public static RowBase getGroupRow(String userCode) throws Exception {
		ValidUtils.notNull(userCode, "用户编号为空,组信息获取失败");
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("userCode", userCode));
		String sql = "select t.*\n"
				+ "  from OC_GROUP t\n"
				+ "  left join OC_GROUP_ASSO t1\n"
				+ "    on t1.group_id = t.guid\n"
				+ " where t1.user_id = @userCode";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (CollectionUtils.isEmpty(rows)) {
			return null;
		} else {
			return rows.get(0);
		}
	}

	/**
	 * 获取组编号
	 * 
	 * @return null表示没有组信息
	 */
	public static String getGroupCode(String userCode) throws Exception {
		RowBase groupRow = getGroupRow(userCode);
		return groupRow == null ? null : groupRow.getSafeString(OperationGroupRow.FIELD_Guid);
	}

	/**
	 * 获取最后一次签到的时间
	 */
	public static DateTime getLastSignInTime(String userCode, String stationCode) throws Exception {
		ValidUtils.notNull(userCode, "用户编号为空,最后一次签到时间获取失败");
		ValidUtils.notNull(stationCode, "用户编号为空,最后一次签到时间获取失败");
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("userCode", userCode));
		parms.add(new GlobalParameter("stationCode", stationCode));
		String sql = "select to_char(max(t.ct), 'yyyy-mm-dd hh24:mi:ss') DateTime\n"
				+ "  from OP_REGI_INFO t\n"
				+ " where t.user_id = @userCode\n"
				+ "   and t.wttp_id = @stationCode";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		// max查询，一定有一条记录
		ValidUtils.notEmpty(rows, "最后一次签到时间获取异常");
		RowBase rowBase = rows.get(0);
		String dateTime = rowBase.getSafeString("DateTime");
		if (StringUtils.IsNullOrSpace(dateTime)) {
			return new DateTime(0);
		} else {
			return DateTime.parse(dateTime, "yyyy-MM-dd HH:mm:ss");
		}
	}

	public static RowBase getSafeUserRow(String userCode) throws Exception {
		ValidUtils.notNull(userCode, "用户编号为空,用户信息获取失败");
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("userCode", userCode));
		String sql = "select * from Safe_User t where t.s_userid = @userCode";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		ValidUtils.notEmpty(rows,"用户编号有误，无法获取用户信息");
		return rows.get(0);
	}

	public static String getCompanyCode(String userCode) throws Exception {
		RowBase row = getSafeUserRow(userCode);
		return row.getSafeString("S_ORGID");
	}
}
